﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Infragistics.Win;
using Infragistics.Win.UltraWinGrid;
using Infragistics.Excel;
namespace BYManageInfoSystem
{
    public partial class Frm_FacCheckSheet : Form
    {
        private DGBY.BLL.BiZhiTab m_bllBiZhiTab = new DGBY.BLL.BiZhiTab();
        private DGBY.BLL.BYCompanyTab m_bllBYCompanyTab = new DGBY.BLL.BYCompanyTab();
        private bool m_bSumStart = false;

 
        private DGBY.BLL.FactoryDataTab m_bllFactoryDataTab = new DGBY.BLL.FactoryDataTab();
        private DGBY.BLL.HxdNoTab m_bllHxdNoTab = new DGBY.BLL.HxdNoTab();
        private DGBY.BLL.FinRecvMoneyDetailTab m_bllFinRecvMoneyDetailTab = new DGBY.BLL.FinRecvMoneyDetailTab();
        UltraGridColumn ultraGridColumn1 = null;
        Decimal dBGJinETotal2 = 0;
        Decimal dtRecvMoneyTemp2 = 0;

        public void LocalPrintPreveiw()
        {
            Infragistics.Shared.ResourceCustomizer rc = Infragistics.Win.Printing.Resources.Customizer;//Resources.Customizer;
            rc.SetCustomizedString("PrintPreview_DialogCaption", "打印预览");
            rc.SetCustomizedString("PrintPreview_Tool_Print", "打印(&P)");
            rc.SetCustomizedString("PrintPreview_Tool_ClosePreview", "关闭(&C)");
            rc.SetCustomizedString("PrintPreview_Tool_ContextMenuPreviewZoom", "显示比例");
            rc.SetCustomizedString("PrintPreview_Tool_Current_Page", "当前页");
            rc.SetCustomizedString("PrintPreview_Tool_Exit", "退出(&X)");
            rc.SetCustomizedString("PrintPreview_Tool_First_Page", "第一页");
            rc.SetCustomizedString("PrintPreview_Tool_Go_To", "跳至");
            rc.SetCustomizedString("PrintPreview_Tool_Last_Page", "最后一页");
            rc.SetCustomizedString("PrintPreview_Tool_Next_Page", "下一页");
            rc.SetCustomizedString("PrintPreview_Tool_Previous_Page", "前一页");
            rc.SetCustomizedString("PrintPreview_Tool_Next_View", "下一视图(&N)");
            rc.SetCustomizedString("PrintPreview_Tool_Previous_View", "前一视图(&P)");
            rc.SetCustomizedString("PrintPreview_Tool_Hand_Tool", "手型工具(&H)");
            rc.SetCustomizedString("PrintPreview_Tool_Page_Setup", "页面设置(&U)");
            rc.SetCustomizedString("PrintPreview_Tool_Snapshot_Tool", "快照工具(&S)");
            rc.SetCustomizedString("PrintPreview_Tool_View", "视图(&V)");
            rc.SetCustomizedString("PrintPreview_Tool_Whole_Page", "合适页");
            rc.SetCustomizedString("PrintPreview_Tool_Zoom", "缩放(&Z)");
            rc.SetCustomizedString("PrintPreview_Tool_Zoom_In", "放大");

            rc.SetCustomizedString("PrintPreview_Tool_Zoom_Out", "缩小");

            rc.SetCustomizedString("PrintPreview_ToolCategory_Context_Menus", "上下文菜单");
            rc.SetCustomizedString("PrintPreview_ToolCategory_File", "文件");
            rc.SetCustomizedString("PrintPreview_ToolCategory_Menus", "菜单");
            rc.SetCustomizedString("PrintPreview_ToolCategory_Tools", "工具栏");
            rc.SetCustomizedString("PrintPreview_ToolCategory_View", "视图");
            rc.SetCustomizedString("PrintPreview_ToolCategory_Zoom_Mode", "缩放模式");

            rc.SetCustomizedString("PrintPreview_ToolTip_ClosePreview", "关闭");
            rc.SetCustomizedString("PrintPreview_ToolTip_Zoom", "缩放");
            rc.SetCustomizedString("StatusBar_Page_X_OF_X", "页:{0}/{1}");

            rc.SetCustomizedString("CustomizeImg_ToolBar_MenuBar", "菜单");
            rc.SetCustomizedString("CustomizeImg_ToolBar_Standard", "标准");
            rc.SetCustomizedString("CustomizeImg_ToolBar_View", "视图");
            rc.SetCustomizedString("PrintPreview_Tool_File", "文件(&F)");
            rc.SetCustomizedString("PrintPreview_Tool_Tools", "工具(&T)");
            rc.SetCustomizedString("PrintPreview_Tool_Dynamic_Zoom_Tool", "动态缩放工具(&D)");
            rc.SetCustomizedString("PrintPreview_Tool_Zoom_Out_Tool", "缩小工具");
            rc.SetCustomizedString("PrintPreview_Tool_Zoom_In_Tool", "放大工具");
            // rc.SetCustomizedString("PrintPreview_Tool_Page_Layout","菜单");
            rc.SetCustomizedString("PreviewRowColSelection_Cancel", "取消");
            rc.SetCustomizedString("PreviewRowColSelection_SelectedPages", "{0} x {1} 页");

            rc.SetCustomizedString("PreviewRowColSelection_Cancel", "取消");
            rc.SetCustomizedString("PrintPreview_Tool_Page_Width", "页宽");
            rc.SetCustomizedString("PrintPreview_ZoomListItem_MarginWidth", "文字宽度");
            rc.SetCustomizedString("PrintPreview_ZoomListItem_PageWidth", "页宽");
            rc.SetCustomizedString("PrintPreview_ZoomListItem_WholePage", "合适页");
            rc.SetCustomizedString("PrintPreview_Tool_Page_Layout", "页面布局");
            rc.SetCustomizedString("PrintPreview_Tool_Margin_Width", "文字宽度");
            rc.SetCustomizedString("ContextMenuPreviewHand", "缩放视图");


            rc.SetCustomizedString("PrintPreview_Tool_Reduce_Page_Thumbnails", "缩小");
            rc.SetCustomizedString("PrintPreview_Tool_Show_Page_Numbers", "显示页号");
            rc.SetCustomizedString("PrintPreview_Tool_ContextMenuThumbnail", "缩略图");
            rc.SetCustomizedString("PrintPreview_Tool_Enlarge_Page_Thumbnails", "放大");
            rc.SetCustomizedString("PrintPreview_Tool_Thumbnails", "缩略图");
            rc.SetCustomizedString("PrintPreview_Tool_Continuous", "连续排序");
            //说明
            rc.SetCustomizedString("StatusBar_DynamicZoom_Instructions", "单击并拖动进行缩放操作");
            rc.SetCustomizedString("StatusBar_Page_X_OF_X", "当前页: {0} / {1}");
            rc.SetCustomizedString("StatusBar_SnapShot_Instructions", "单击并拖动,系统将选定矩型区域复制到剪帖板");
            rc.SetCustomizedString("StatusBar_ZoomIn_Instructions", "单击并拖动,系统将放大选定矩型区域");
            rc.SetCustomizedString("StatusBar_ZoomOut_Instructions", "单击并拖动,系统将缩小选定矩型区域");
            rc.SetCustomizedString("StatusBar_Hand_Instructions", "单击并拖动以便显示更多内容");
        }
        private void InitCombox()
        {
            DataSet ds = m_bllBYCompanyTab.GetList("");
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                this.comboBox2Comp.DataSource = ds.Tables[0];
                this.comboBox2Comp.DisplayMember = "经营单位名称";
                this.comboBox2Comp.ValueMember = "BYCompanyTabID";
                this.comboBox2Comp.SelectedIndex = 0;
            }

            ds = m_bllFactoryDataTab.GetList("");
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                this.comboBoxFac.DataSource = ds.Tables[0];

                this.comboBoxFac.ValueMember = "FactoryDataTabID";
                this.comboBoxFac.DisplayMember = "工厂名称";
                this.comboBoxFac.SelectedIndex = 0;
            }
        }

        public Frm_FacCheckSheet()
        {
            InitializeComponent();
        }

        private void Frm_FacCheckSheet_Load(object sender, EventArgs e)
        {
            InitCombox();
            LocalPrintPreveiw();
            //InitUltraWinGridSetting();
        }
        /// 取得某月的第一天
        /// </summary>
        /// <param name="datetime">要取得月份第一天的时间</param>
        /// <returns></returns>
        private DateTime FirstDayOfMonth(DateTime datetime)
        {
            return datetime.AddDays(1 - datetime.Day);
        }
        /// <summary>
        /// 取得某月的最后一天
        /// </summary>
        /// <param name="datetime">要取得月份最后一天的时间</param>
        /// <returns></returns>
        private DateTime LastDayOfMonth(DateTime datetime)
        {
            return datetime.AddDays(1 - datetime.Day).AddMonths(1).AddDays(-1);
        }
        private List<DateTime> FillDateTimeList(DataSet ds)
        {
            List<DateTime> lstDt = new List<DateTime>();
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                DateTime dtFirst = (DateTime)ds.Tables[0].Rows[0]["申报日期"];
                DateTime dtLast = (DateTime)ds.Tables[0].Rows[ds.Tables[0].Rows.Count - 1]["申报日期"];

                for (int nYear = dtFirst.Year; nYear <= dtLast.Year; nYear++)
                {
                    int nFirstMonth = (nYear == dtFirst.Year) ? dtFirst.Month : 1;
                    int nEndMonth = (nYear == dtLast.Year) ? dtLast.Month : 12;
                    for (nFirstMonth = nFirstMonth; nFirstMonth <= nEndMonth; nFirstMonth++)
                    {
                        DateTime dtTemp = new DateTime(nYear, nFirstMonth, 1);
                        lstDt.Add(dtTemp);
                    }
                }

            }

            return lstDt;
        }

        private void AddNewColumn(DataTable dt)
        {
            int nIndex = 0;
            DataSet ds = m_bllBiZhiTab.GetList("");

            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                nIndex = ds.Tables[0].Rows.Count + 5;
            }


            dt.Columns.Add("币种", System.Type.GetType("System.String"));
            dt.Columns["币种"].SetOrdinal(nIndex++);
            dt.Columns.Add("收款日期", System.Type.GetType("System.DateTime"));
            dt.Columns["收款日期"].SetOrdinal(nIndex++);

            dt.Columns.Add("收款金额", System.Type.GetType("System.Decimal"));
            dt.Columns["收款金额"].SetOrdinal(nIndex++);



            dt.Columns.Add("折算汇率", System.Type.GetType("System.Decimal"));
            dt.Columns["折算汇率"].SetOrdinal(nIndex++);


            dt.Columns.Add("折算金额", System.Type.GetType("System.Decimal"));
            dt.Columns["折算金额"].SetOrdinal(nIndex++);
            dt.Columns.Add("折合RMB", System.Type.GetType("System.Decimal"));
            dt.Columns["折合RMB"].SetOrdinal(nIndex++);
            dt.Columns.Add("差额", System.Type.GetType("System.Decimal"));
            dt.Columns["差额"].SetOrdinal(nIndex++);
        }

        private Decimal GetThisMonthTotalBGJinE(DataSet ds)
        {
            List<string> lstContract = new List<string>();

            Decimal dBGJinETotal = 0;
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                string strContract = ds.Tables[0].Rows[i]["合同编号"].ToString();
                if (lstContract.Contains(strContract))
                    continue;
                string strBGJinE = ds.Tables[0].Rows[i]["美元"].ToString();
                Decimal dBGJinE = Convert.ToDecimal(strBGJinE);
                dBGJinETotal += dBGJinE;
                lstContract.Add(strContract);
            }

            return dBGJinETotal;
        }

        private Decimal GetThisMonthTotalRecvJinE(DataSet ds)
        {

            Decimal dtRecvMoneyTemp = 0;
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                string strRecvMoney = ds.Tables[0].Rows[i]["折算金额"].ToString();
                Decimal dtRecvMoney = Convert.ToDecimal(strRecvMoney);
                dtRecvMoneyTemp += dtRecvMoney;
            }

            return dtRecvMoneyTemp;
        }
        private void ShowAllRecord(string strWhere)
        {
            ultraGrid1.DataSource = null;
            DataSet ds = m_bllHxdNoTab.ShowFacCheckSheetRec(strWhere);

            List<DateTime> lstDt = FillDateTimeList(ds);
            lstDt.Sort();


            DateTime dtFirstDayTemp = FirstDayOfMonth(this.dateTimePickerFrom.Value);
            DateTime dtLastDayTemp = LastDayOfMonth(this.dateTimePickerFrom.Value);

            Decimal dOrigQiChuRemainJinE = Convert.ToDecimal(this.textBoxQiChuRemainJinE.Text);
            Decimal dNextMonthRemainJinETemp = dOrigQiChuRemainJinE;//上个月期末余额

            Decimal dtThisMonthRemainJinETemp = 0;

            //计算出上个月期初余额
            foreach (DateTime dt in lstDt)
            {
                Decimal dBGJinETotal  = 0;
                Decimal dtRecvMoneyTemp = 0;
                DateTime dtFirstDay = FirstDayOfMonth(dt);
                DateTime dtLastDay = LastDayOfMonth(dt);

                ds = m_bllHxdNoTab.ShowFacCheckSheetRec(strWhere + " and BGDComeBackTab.SBDate >= '" + dtFirstDay + "'" + " and BGDComeBackTab.SBDate <= '" + dtLastDay + "'");

                if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    dBGJinETotal = GetThisMonthTotalBGJinE(ds);
                }

                DataSet dsRecvMoney = m_bllFinRecvMoneyDetailTab.ShowRecvMoneyDetailRec(strWhere + " and RecvMoneyDate >= '" + dtFirstDay + "'" + " and RecvMoneyDate <= '" + dtLastDay + "'");

                if (dsRecvMoney.Tables.Count > 0 && dsRecvMoney.Tables[0].Rows.Count > 0)
                {
                    dtRecvMoneyTemp = GetThisMonthTotalRecvJinE(dsRecvMoney);
                }

                dBGJinETotal2 = dBGJinETotal;
                dtRecvMoneyTemp2 = dtRecvMoneyTemp;
                if (dtFirstDay.Date.CompareTo(dtFirstDayTemp.Date) == 0)
                {
                    this.textBoxLastMonthMoney.Text = dNextMonthRemainJinETemp.ToString();
                    break;
                }

                dtThisMonthRemainJinETemp = dNextMonthRemainJinETemp;
                dNextMonthRemainJinETemp = dtThisMonthRemainJinETemp + dBGJinETotal - dtRecvMoneyTemp;
            }
            
            ds = m_bllHxdNoTab.ShowFacCheckSheetRec(strWhere + " and BGDComeBackTab.SBDate >= '" + dtFirstDayTemp + "'" + " and BGDComeBackTab.SBDate <= '" + dtLastDayTemp + "'");

            int nLineCounts = ds.Tables[0].Rows.Count;
            AddNewColumn(ds.Tables[0]);
            DataTable dtNew = null;
            dtNew = ds.Tables[0].Clone();
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                dtNew.Rows.Add(ds.Tables[0].Rows[i].ItemArray);
            }
            DataSet dsRecvMoneyTemp = m_bllFinRecvMoneyDetailTab.ShowRecvMoneyDetailRec(strWhere + " and RecvMoneyDate >= '" + dtFirstDayTemp + "'" + " and RecvMoneyDate <= '" + dtLastDayTemp + "'");

            int nRecvMoneyLineCounts = dsRecvMoneyTemp.Tables[0].Rows.Count;

            if (nRecvMoneyLineCounts > nLineCounts)
            {
                int nNeedAddLines = nRecvMoneyLineCounts - nLineCounts;
                for (int nIndex = 0; nIndex < nNeedAddLines; nIndex++)
                {
                    DataRow dr1 = dtNew.NewRow();
                    dtNew.Rows.Add(dr1);
                }
            }

            for (int i = 0; i < dsRecvMoneyTemp.Tables[0].Rows.Count; i++)
            {
                dtNew.Rows[i]["币种"] = dsRecvMoneyTemp.Tables[0].Rows[i]["币种"];
                dtNew.Rows[i]["收款日期"] = dsRecvMoneyTemp.Tables[0].Rows[i]["收款日期"];
                dtNew.Rows[i]["收款金额"] = dsRecvMoneyTemp.Tables[0].Rows[i]["收款金额"];
                dtNew.Rows[i]["折算汇率"] = dsRecvMoneyTemp.Tables[0].Rows[i]["折算汇率"];
                dtNew.Rows[i]["折算金额"] = dsRecvMoneyTemp.Tables[0].Rows[i]["折算金额"];
                dtNew.Rows[i]["折合RMB"] = 0;
           }



            if (dtNew.Rows.Count > 0)
            {

                DataRow dr = dtNew.NewRow();
                dr["合同编号"] = "合计";
                dr["美元"] = dBGJinETotal2;
                dr["折算金额"] = dtRecvMoneyTemp2;
                dr["差额"] = dNextMonthRemainJinETemp + dBGJinETotal2 - dtRecvMoneyTemp2;
                dtNew.Rows.Add(dr);
                
                ultraGrid1.DataSource = dtNew;
                this.ultraGrid1.DisplayLayout.Bands[0].Columns["集装箱柜号"].CellMultiLine = Infragistics.Win.DefaultableBoolean.True;
                //ConvertStringToMultiLineFirst();

                ultraGrid1.DisplayLayout.Bands[0].Columns[0].Hidden = true;
                ultraGrid1.DisplayLayout.Bands[0].Columns[4].Hidden = true;
                ProcessMultiHeader();
            }
        }
        void ConvertStringToMultiLineFirst()
        {
            foreach (UltraGridColumn column2 in ultraGrid1.DisplayLayout.Bands[0].Columns)
            {
                string str = column2.Header.Caption;
                if (str == "集装箱柜号")
                {
                    int nRowsCount = this.ultraGrid1.Rows.Count;
                    for (int i = 0; i < nRowsCount; i++)
                    {
                        string strSource = this.ultraGrid1.Rows[i].GetCellValue(column2).ToString();
                        ConvertStringToMultiLineSecond(strSource, i);
                    }
                }
            }
        }

        private void ConvertStringToMultiLineSecond(string strSource, int nRowIndex)
        {
            string str = strSource.ToString();
            string[] arr = str.Split(',');
            string tempa = "";
            int n = 0;
            foreach (string ch in arr)
            {

                if (ch != "")
                {
                    if (n == 0)
                        tempa += ch;
                    else
                        tempa += "\n" + ch;
                }

                n++;
            }

            this.ultraGrid1.Rows[nRowIndex].Cells["集装箱柜号"].Value = tempa;
        }

        private void ProcessMultiHeader()
        {
            int nSpanX = 0;
            int nOriginX = 0;
            int nOriginXTemp = 0;
            bool bSpan = false;
            foreach (UltraGridColumn column2 in this.ultraGrid1.DisplayLayout.Bands[0].Columns)
            {
                string str = column2.Header.Caption;
                if (str == "币种")
                    bSpan = false;

                if (!bSpan)
                {
                    if (str == "报关单金额")
                        continue;
                    column2.RowLayoutColumnInfo.SpanY = 4;
                    if (column2.Hidden == false)
                        nOriginXTemp += 2;
                }
                else
                {
                    nSpanX += 2;
                    column2.RowLayoutColumnInfo.OriginY = 2;
                }
                if (str == "BGPackingTabID")
                {
                    bSpan = true;
                    nOriginX = nOriginXTemp;
                }
            }

            ultraGridColumn1 = ultraGrid1.DisplayLayout.Bands[0].Columns.Add("报关单金额", "报关单金额");
            ultraGridColumn1.CellActivation = Activation.Disabled;
            ultraGridColumn1.SortIndicator = SortIndicator.Disabled;
            ultraGridColumn1.RowLayoutColumnInfo.PreferredCellSize = new Size(1, 1);
            ultraGridColumn1.RowLayoutColumnInfo.AllowLabelSizing = RowLayoutSizing.None;
            ultraGridColumn1.RowLayoutColumnInfo.LabelPosition = LabelPosition.LabelOnly;
            ultraGridColumn1.RowLayoutColumnInfo.OriginX = nOriginX;//有2个列隐藏了
            ultraGridColumn1.RowLayoutColumnInfo.OriginY = 0;
            ultraGridColumn1.RowLayoutColumnInfo.SpanX = nSpanX;
            ultraGridColumn1.RowLayoutColumnInfo.SpanY = 2;
        }
        private void buttonQuery_Click(object sender, EventArgs e)
        {
            //DateTime dtFirstDay = FirstDayOfMonth(this.dateTimePickerFrom.Value);
            //DateTime dtLastDay = LastDayOfMonth(this.dateTimePickerFrom.Value);
            ShowAllRecord(@"BYCompanyTab.BYCompanyTabID = '" + this.comboBox2Comp.SelectedValue + "'"
                                                                           + " and FactoryDataTab.FactoryDataTabID = '" + this.comboBoxFac.SelectedValue + "'");

        }

        private void comboBoxFac_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                string str = this.comboBoxFac.SelectedValue.ToString();

                int nID = Convert.ToInt32(str, 10);
                DataSet ds = m_bllFactoryDataTab.GetList("FactoryDataTabID = '" + nID + "'");
                if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    this.textBoxQiChuRemainJinE.Text = ds.Tables[0].Rows[0]["期初余额"].ToString();
                }
            }
            catch { }

        }

        private void ultraGrid1_InitializeLayout(object sender, InitializeLayoutEventArgs e)
        {
            ultraGrid1.DisplayLayout.Bands[0].UseRowLayout = true;
            ultraGrid1.DisplayLayout.Override.RowSizing = RowSizing.AutoFree;
            //foreach (UltraGridColumn ugc in ultraGrid1.DisplayLayout.Bands[0].Columns)
            //    ugc.PerformAutoResize();
        }

        private void buttonExportToExcel_Click(object sender, EventArgs e)
        {
            DataTable dt = (DataTable)ultraGrid1.DataSource;

            if (dt != null)
            {
                if (dt.Rows.Count > 0)
                {
                    SaveFileDialog sfd = new SaveFileDialog();
                    sfd.Filter = "Excel文件(*.xls)|*.xls";
                    string outFile = "";

                    if (sfd.ShowDialog() == DialogResult.OK)
                    {
                        outFile = sfd.FileName;
                    }
                    else
                    {
                        return;
                    }

                    ultraGridExcelExporter1.Export(ultraGrid1, outFile);
                }
            }
        }

        private void buttonPrint_Click(object sender, EventArgs e)
        {
            this.ultraGridPrintDocument2.Grid = this.ultraGrid1;
            this.ultraPrintPreviewDialog2.Document = this.ultraGridPrintDocument2;
            this.ultraPrintPreviewDialog2.ShowDialog(this);
        }

        private void ultraGridExcelExporter1_CellExported(object sender, Infragistics.Win.UltraWinGrid.ExcelExport.CellExportedEventArgs e)
        {
            Worksheet ws = e.CurrentWorksheet;
            if (e.CurrentColumnIndex == 0)
            {
                Infragistics.Excel.IWorksheetCellFormat cfCellFmt;
                Infragistics.Excel.IWorksheetCellFormat cfCellFmt2;
                string stringt = "";
                if (e.Value != null)
                {
                    stringt = e.Value.ToString();
                    //e.Value = null;
                }
                if (stringt == "合计")
                {
                    int iRdex = e.CurrentRowIndex;
                    int iCdex = e.CurrentColumnIndex;
                    //ws.Rows[iRdex].Cells[iCdex - 1].Value = "";

                    cfCellFmt = e.CurrentWorksheet.Rows[iRdex].Cells[iCdex].CellFormat;
                    cfCellFmt.LeftBorderStyle =
          Infragistics.Excel.CellBorderLineStyle.None;
                    cfCellFmt2 = e.CurrentWorksheet.Rows[iRdex].Cells[iCdex + 2].CellFormat;
                    cfCellFmt2.RightBorderStyle =
          Infragistics.Excel.CellBorderLineStyle.None;

                    // Create a merged region that will be a header to the column headers
                    Infragistics.Excel.WorksheetMergedCellsRegion mergedRegion1 =
                      ws.MergedCellsRegions.Add(iRdex, iCdex , iRdex, iCdex + 2);

                    // Set the value of the merged region
                    mergedRegion1.Value = stringt;

                    // Give the merged region a solid background color
                    mergedRegion1.CellFormat.FillPattern =
                      Infragistics.Excel.FillPatternStyle.Solid;
                    mergedRegion1.CellFormat.FillPatternForegroundColor = Color.White;

                    // Set the cell alignment of the middle cell in the merged region.
                    // Since a cell and its merged region shared a cell format, this will 
                    // ultimately set the format of the merged region
                    ws.Rows[iRdex].Cells[iCdex].CellFormat.Alignment =
                      Infragistics.Excel.HorizontalCellAlignment.Center;
                }

            }
        }

        private void ultraGridExcelExporter1_BeginExport(object sender, Infragistics.Win.UltraWinGrid.ExcelExport.BeginExportEventArgs e)
        {
            DataSet ds = m_bllFactoryDataTab.GetList("FactoryDataTab.FactoryName = '" +this.comboBoxFac.Text + "'");
            string strProduct = ds.Tables[0].Rows[0]["货物名称"].ToString();
            string strFacAbbr = ds.Tables[0].Rows[0]["工厂简称"].ToString();

            DateTime dtFirst = FirstDayOfMonth(this.dateTimePickerFrom.Value);
            string strYear = dtFirst.Year.ToString();
            string strMonth = dtFirst.Month.ToString();

            Worksheet ws = e.CurrentWorksheet;
            Decimal dtTemp = Convert.ToDecimal(this.textBoxLastMonthMoney.Text);
            Decimal dtTemp2 = dtTemp + dBGJinETotal2 - dtRecvMoneyTemp2;

            //ws.Columns[4].Width = 20 * 256;
            //ws.Columns[5].Width = 20 * 256;
            ws.Rows[1].Cells[4].CellFormat.Alignment = Infragistics.Excel.HorizontalCellAlignment.Center;
            ws.Rows[2].Cells[4].CellFormat.Alignment = Infragistics.Excel.HorizontalCellAlignment.Center;

            ws.Rows[1].Cells[4].Value = strYear + "年" + strMonth + "月" + strFacAbbr + "的对账单";
            ws.Rows[2].Cells[0].Value = this.comboBoxFac.Text + @"：";

            ws.Rows[3].Cells[1].Value = @"我司于" + strYear + "年" + strMonth + "月" + "出口贵司生产" + strProduct + @"一批，出口额USD" + dBGJinETotal2.ToString() + "元，上月余额USD" + this.textBoxLastMonthMoney.Text + "元， 我司已收到贵司结汇USD" + dtRecvMoneyTemp2.ToString() + "元，";
            ws.Rows[4].Cells[3].Value = "因此还需结汇USD" + dtTemp2.ToString() + "元，请贵司尽快将货款汇到我公司账上。谢谢！";
            ws.Rows[5].Cells[0].Value = "明细如下：";
            ws.Rows[5].Cells[2].Value = "期初余额：" + dtTemp.ToString();

            //ws.Rows[4].Cells[3].Value = @"经营单位电话：0769-22423198";
            //ws.Rows[4].Cells[6].Value = @"经营单位传真：0769-22429650";
            //e.CurrentWorksheet.Workbook.ActiveWorksheet = ws;

            e.CurrentRowIndex = 6;
            //e.CurrentColumnIndex = 2;
        }

        private void ultraGridExcelExporter1_EndExport(object sender, Infragistics.Win.UltraWinGrid.ExcelExport.EndExportEventArgs e)
        {
            DataSet ds = m_bllBYCompanyTab.GetList("BYCompanyTabID = '" + this.comboBox2Comp.SelectedValue + "'");
            string strPerson = ds.Tables[0].Rows[0]["经理姓名"].ToString();
            string strTel = ds.Tables[0].Rows[0]["电话"].ToString();

            Worksheet ws = e.CurrentWorksheet;
            int a = e.CurrentRowIndex;


            ws.Rows[a + 1].Cells[0].Value = "联系人：" + strPerson;
            ws.Rows[a + 1].Cells[6].Value = this.comboBox2Comp.Text;
            ws.Rows[a + 2].Cells[0].Value = "联系电话：" + strTel.ToString();
            ws.Rows[a + 2].Cells[6].Value = DateTime.Now.Date.ToShortDateString();
        }

        private void buttonSumStart_Click(object sender, EventArgs e)
        {
            m_bSumStart = true;
            Infragistics.Win.UltraWinGrid.UltraGridRow[] ugr = ultraGrid1.Rows.GetFilteredInNonGroupByRows();
            for (int i = 0; i < ugr.Length; i++)
            {
                for (int j = 0; j < ugr[i].Cells.Count; j++)
                {
                    if (ugr[i].Cells[j].Appearance.BackColor == Color.Aqua)
                    {
                        ugr[i].Cells[j].Appearance.BackColor = Color.FromName("0");
                    }
                }
            }
        }

        private void buttonSumStop_Click(object sender, EventArgs e)
        {
            try
            {
                Decimal dtTotal = 0;
                Infragistics.Win.UltraWinGrid.UltraGridRow[] ugr = ultraGrid1.Rows.GetFilteredInNonGroupByRows();
                for (int i = 0; i < ugr.Length; i++)
                {
                    for (int j = 0; j < ugr[i].Cells.Count; j++)
                    {
                        if (ugr[i].Cells[j].Appearance.BackColor == Color.Aqua)
                        {
                            Decimal dtTemp = Convert.ToDecimal(ugr[i].Cells[j].Value.ToString());
                            dtTotal += dtTemp;
                            //ugr[i].Cells[j].Appearance.BackColor = Color.FromName("0");
                        }
                    }
                }

                this.textBoxTotal.Text = dtTotal.ToString();
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.ToString(), "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
        }
        private void ultraGrid1_MouseDown(object sender, MouseEventArgs e)
        {
            UltraGrid grid = (UltraGrid)sender;
            UIElement element = grid.DisplayLayout.UIElement.LastElementEntered;

            Infragistics.Win.UltraWinGrid.ColumnHeader columnHeader = element.GetContext(typeof(Infragistics.Win.UltraWinGrid.ColumnHeader)) as Infragistics.Win.UltraWinGrid.ColumnHeader;
            if (columnHeader != null)
            {
                string str = columnHeader.Caption;
                if (str != "合同金额" && str != "报关金额")
                    return;
                Infragistics.Win.UltraWinGrid.UltraGridRow[] ugr = ultraGrid1.Rows.GetFilteredInNonGroupByRows();
                if (ugr.Length <= 0)
                {
                    return;
                }
                Decimal dtTemp = 0;
                for (int i = 0; i < ugr.Length; i++)
                {
                    string strTemp = ugr[i].Cells[str].Value.ToString();
                    if (strTemp != "")
                        dtTemp += Convert.ToDecimal(ugr[i].Cells[str].Value.ToString());
                }
                this.textBoxTotal.Text = dtTemp.ToString();


                //if(bool f=age is int;


                // Set the grid's tooltip here
                // Debug.WriteLine("ToolTip");
            }
            else
            {
                // Set the grid's tooltip to null. 
                //  Debug.WriteLine("null");
            }
        }
        private void ultraGrid1_AfterCellActivate(object sender, EventArgs e)
        {
            int iIndex = this.ultraGrid1.ActiveCell.Row.Index;
            if (iIndex < 0)
                return;
           // m_nDelIndex = ultraGrid1.ActiveRow.Index;
            if (m_bSumStart)
            {
                if (this.ultraGrid1.ActiveCell.Appearance.BackColor.Name == "0")
                {
                    this.ultraGrid1.ActiveCell.Appearance.BackColor = Color.Aqua;
                }
                else
                {
                    this.ultraGrid1.ActiveCell.Appearance.BackColor = Color.FromName("0");
                }
            }
        }


    }
}
